import collections
import re
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
from apps.storage.models.template import Template, TemplateFieldEnum
from apps.storage.models.data import DataMeta
from apps.storage.utils.serializers.data import *
from django.utils.encoding import smart_text
from django.utils.translation import ugettext, ugettext_noop as _
from django.conf import settings
from .json import JSONSerializer
from .common import *
from mongoengine import DoesNotExist


def _force_text(cell_value):
    if cell_value is not None:
        return str(cell_value)


class Cursor(collections.Mapping):
    """
    游标，记录某个sheet当前处理到哪个位置
    """

    def __init__(self, row=1, column=1):
        self.row = row
        self.column = column

    def move_right(self):
        self.column += 1
        return self

    def move_down(self):
        self.column += 1
        return self

    def move_to_new_line(self, row=None, col_start_at=2):
        """
        移动到新的行col_start_at对应的列，如果未指定row则移动到下一行
        :param row: 新的行号
        :param col_start_at: 列
        :return:
        """
        if row:
            self.row = row
        else:
            self.row += 1
        self.column = col_start_at
        return self

    def move_to_line_head(self, col_start_at=2):
        """
        移动到行的第一个单元格（col_start_at指定的列视为第一列）
        :param col_start_at: 列
        :return:
        """
        self.column = col_start_at
        return self

    def get_line_head(self, col_start_at=1):
        return Cursor(row=self.row, column=col_start_at)

    def to_coordinate(self):
        """
        转为Excel字母+数字的坐标形式
        :return:
        """
        return get_column_letter(self.column) + str(self.row)

    """
    下列函数用于方便使用openpyxl的cell函数（**解包坐标）
    """

    def __getitem__(self, item):
        if item == 'row':
            return self.row
        elif item == 'column':
            return self.column

    def __len__(self):
        return 2

    def __iter__(self):
        return iter({'row': self.row, 'column': self.column})


# 字段坐标
class FieldCoordinate:
    def __init__(self, sheet_name, row, column, at_start, at_end, in_array):
        """
        :param sheet_name: sheet名称
        :param row: header所在行
        :param column: 列
        :param at_start: 是否是某行第一个数据字段（除去Data ID和Table ID）
        :param at_end: 是否是某行最后一个字段
        :param in_array: 是否在数组中
        """
        self.sheet_name = sheet_name
        self.row = row
        self.column = column
        self.at_start = at_start
        self.at_end = at_end
        self.in_array = in_array


class TableRowCursor:
    """
    如果Table类型字段嵌在数组中，对应的Sheet需要用这个Cursor来遍历，
    因为同一个DataID的Row可能属于不同的Table（不同的数组元素）
    """

    def __init__(self, start_row=1, table_id_column=2):
        self._row = start_row
        self._column = table_id_column
        self.current_table_id = None

    @property
    def row(self):
        return self._row

    def has_next_row(self, sheet, data_id, ignore_table_id=False):
        """
        判断下一行是否属于data_id对应的数据中的表格（并且是当前表格）中的行
        :param sheet: sheet
        :param data_id: 当前数据ID
        :param ignore_table_id: 无视Table ID，强制读取所有属于data_id的行
        :return: True or False
        """
        self._row += 1
        target_data_id = _force_text(sheet.cell(row=self._row, column=1).value)
        if target_data_id == data_id:
            if ignore_table_id:
                return True
            table_id = _force_text(sheet.cell(row=self._row, column=self._column).value)
            if self.current_table_id is None and table_id is not None:
                self.current_table_id = table_id
                return True
            elif table_id is not None and table_id == self.current_table_id:
                return True
        self._row -= 1
        self.current_table_id = None
        return False


class ExcelHandler:
    """
    通用函数
    """

    # 数据元信息sheet名称
    @property
    def _meta_sheet_name(self):
        return ugettext('Data Info')

    # 第一级数据sheet名称
    @property
    def _root_data_name(self):
        return "1"

    def _get_cursor_by_field_path(self, path_str_or_list):
        """
        通过字段path获取对应的sheet的cursor
        :param path_str_or_list:
        :return:
        """
        coordinate = self._get_field_coordinate_by_field_path(path_str_or_list)
        return self._sheet_name_cursor_map[coordinate.sheet_name]

    def _get_field_coordinate_by_field_path(self, path_str_or_list):
        """
        通过字段path获取其header的坐标
        :param path_str_or_list:
        :return:
        """
        if isinstance(path_str_or_list, list):
            path_str = '.'.join(path_str_or_list)
        else:
            assert isinstance(path_str_or_list, str)
            path_str = path_str_or_list
        return self._path_str_coordinate_map[path_str]

    def _get_sheet_by_field_path(self, path_str_or_list):
        """
        通过字段path获取对应的sheet
        :param path_str_or_list:
        :return:
        """
        coordinate = self._get_field_coordinate_by_field_path(path_str_or_list)
        return self._wb[coordinate.sheet_name]

    def _get_sheet_name_by_field_path(self, path_str_or_list):
        """
        通过字段path获取对应的sheet的名称
        :param path_str_or_list:
        :return:
        """
        return self._get_field_coordinate_by_field_path(path_str_or_list).sheet_name

    def _create_data_sheet(self, title=None, path_str_or_list=None):
        """
        建一个新的sheet
        :param title: sheet名称，如果不提供则使用sheet的编号作为sheet名称
        :param path_str_or_list:
        :return:
        """
        ws = self._wb.create_sheet(title)
        if title is None:
            ws.title = str(self._wb.index(ws))
        if isinstance(path_str_or_list, list):
            path_str = '.'.join(path_str_or_list)
        else:
            path_str = path_str_or_list
        if path_str is not None:
            self._sheet_name_path_str_map_list.append((ws.title, path_str))
        self._sheet_name_cursor_map[ws.title] = Cursor()
        ws.cell(row=1, column=1).value = ugettext('Data ID')
        return ws

    def _create_headers_from_field(self, sheet, field_path_list, t, misc, fill_header=True, at_start=False,
                                   at_end=False, in_array=False):
        """
        根据字段填写表头
        :param sheet: 需要填写的sheet
        :param field_path_list: 字段path（列表形式）
        :param t: 字段的t（来自模板）
        :param misc: 字段的misc（来自模板）
        :param fill_header: 是否填写改字段
        :param at_start: 该字段是否为sheet的第一个数据字段（除了Data ID和Table ID等）
        :param at_end: 该字段是否为sheet的最后一个字段
        :return: None
        """

        path_str = '.'.join(field_path_list)  # path字符串
        self._path_str_field_meta_map[path_str] = {'t': t, 'misc': misc}  # 保存字段的meta信息

        cursor = self._sheet_name_cursor_map[sheet.title]  # 获取sheet的游标
        cursor.move_right()  # 先右移cursor再填写
        if fill_header:
            unit = misc.get('unit')
            if unit is not None:
                sheet.cell(**cursor).value = field_path_list[-1] + f' ({unit})'  # 填写表头
            else:
                sheet.cell(**cursor).value = field_path_list[-1]

        # 记录表头坐标
        self._path_str_coordinate_map[path_str] = FieldCoordinate(sheet.title, cursor.row,
                                                                  cursor.column, at_start, at_end, in_array)
        t = TemplateFieldEnum(t)
        if t.is_table:
            ws = self._create_data_sheet(path_str_or_list=field_path_list)  # 为表格建一个sheet
            self._path_str_t_map[path_str] = t
            if in_array:
                ws.cell(row=1, column=2).value = ugettext('Table ID')  # sheet的第二列是Table ID
                self._sheet_name_cursor_map[ws.title].move_right()

            self._path_str_table_cursor_map[path_str] = TableRowCursor()
            _ord = misc['_head']
            start = _ord[0]  # 第一个字段
            end = _ord[-1]  # 最后一个字段
            for field_name in _ord:  # 填写表格的每一列
                self._create_headers_from_field(ws, field_path_list + [field_name], t=misc[field_name]['t'],
                                                misc=misc[field_name].get('misc', {}),
                                                at_start=field_name == start,
                                                at_end=field_name == end,
                                                in_array=in_array)
        elif t.is_container or t.is_generator:
            ws = self._create_data_sheet(path_str_or_list=field_path_list)  # 为容器或生成器建一个sheet
            self._path_str_t_map[path_str] = t
            _ord = misc['_ord'] if '_ord' in misc else misc['_opt']
            start = _ord[0]
            end = _ord[-1]
            for field_name in _ord:
                if self._mode == DataMode.TEMPLATE:
                    self._create_headers_from_field(ws, field_path_list + [field_name], t=misc[field_name]['t'],
                                                    misc=misc[field_name].get('misc', {}),
                                                    at_start=field_name == start,
                                                    at_end=field_name == end,
                                                    in_array=in_array)
                else:
                    # 填写每个字段。生成器的每个元素都认为是sheet中第一个也是最后一个字段，因为生成器的sheet每一行只有一个字段有值
                    self._create_headers_from_field(ws, field_path_list + [field_name], t=misc[field_name]['t'],
                                                    misc=misc[field_name].get('misc', {}),
                                                    at_start=True if t.is_generator else field_name == start,
                                                    at_end=True if t.is_generator else field_name == end,
                                                    in_array=in_array)
        elif t.is_array:
            ws = self._create_data_sheet(path_str_or_list=field_path_list)  # 为数组建一个sheet
            self._path_str_t_map[path_str] = t
            ws.cell(row=1, column=2).value = ugettext('Item Index')  # 第二列是Item Index
            ws.cell(row=1, column=3).value = ugettext('Items')  # 第三列是Items
            self._sheet_name_cursor_map[ws.title].move_right()
            t = misc['t']
            misc = misc.get('misc', {})
            """
            为数组的元素填写表头（元素本身不填写表头，因为元素的表头是Items，fill_header为False。
            但是数组元素可能是表格生成器或者容器，要为它们的子元素填写表头），数组元素的path记为数组的path+元素类型名称
            """
            self._create_headers_from_field(ws, field_path_list + [TemplateFieldEnum(t).description], t=t,
                                            misc=misc, fill_header=False, at_start=True, at_end=True,
                                            in_array=True)

        elif t.is_choice:
            if self._mode == DataMode.TEMPLATE:
                from openpyxl.worksheet.datavalidation import DataValidation
                dv = DataValidation(type="list", formula1=f"\"{', '.join(choices_to_list(misc))}\"", allow_blank=True)
                column_letter = get_column_letter(cursor.column)
                dv.ranges.add(f'{column_letter}2:{column_letter}1048576')
                sheet.add_data_validation(dv)

    def _create_meta_sheet(self, is_template_mode=False):
        """
        创建元数据sheet
        :param is_template_mode: 指明建立的是否是导入模板的元数据sheet
        :return:
        """
        ws = self._wb.create_sheet(self._meta_sheet_name)
        ws['A1'] = ugettext("Data ID")
        if is_template_mode:  # 将template id记录在sheet中并隐藏
            template_id_cell = ws.cell(row=1, column=len(DataMetaFieldEnum.reader_meta_fields()) + 2)
            template_id_cell.value = str(self._template.id) + ugettext(' (DO NOT EDIT OR DELETE THIS CELL !!!!!!!!)')
            template_id_cell.number_format = ';;;'  # 隐藏单元格
            iterator = DataMetaFieldEnum.reader_iterator()
        else:
            iterator = DataMetaFieldEnum.writer_iterator()
        for index, field in enumerate(iterator):
            ws.cell(row=1, column=2 + index).value = field.description

    def _create_map_sheet(self):
        """
        建立目录sheet
        :return:
        """
        if len(self._sheet_name_path_str_map_list) == 0:
            return

        ws = self._wb.create_sheet(ugettext('Sheet Info'), index=0)
        ws['A1'].value = ugettext('Sheet Name')
        ws['B1'].value = ugettext('Sheet Content')
        ws['A2'].value = '1'
        ws['B2'].value = f'=HYPERLINK("#{1}!A2", "{ugettext("Root Data")}")'
        ws['B2'].style = 'Hyperlink'
        row = 3

        for sheet_name, path_str in self._sheet_name_path_str_map_list:
            t = self._path_str_t_map[path_str]
            detail = path_str
            if t.is_array:
                detail = ugettext("Elements of array '%(field_name)s'") % {'field_name': path_str}
            elif t.is_container:
                detail = ugettext("Data of container '%(field_name)s'") % {'field_name': path_str}
            elif t.is_generator:
                detail = ugettext("Options of generator '%(field_name)s'") % {'field_name': path_str}
            elif t.is_table:
                detail = ugettext("Data of table '%(field_name)s'") % {'field_name': path_str}
            ws.cell(row=row, column=1).value = sheet_name
            cell = ws.cell(row=row, column=2)
            cell.value = f'=HYPERLINK("#{sheet_name}!A1", "{detail}")'
            cell.style = 'Hyperlink'
            row += 1

    def __init__(self, mode, template: Template = None, exclude=()):
        """
        初始化
        :param mode: 操作模式
        :param template: 数据模板，如果是write或者template模式则必须提供
        :param exclude: 不导出的字段，仅在write模式使用
        """
        self._mode = mode
        self._template = None
        self._meta_cursor = Cursor(row=2, column=1)  # 数据元信息Sheet游标
        self._sheet_name_cursor_map = {}
        self._path_str_coordinate_map = {}
        self._path_str_field_meta_map = {}
        self._path_str_sheet_name_map = {}
        self._path_str_table_cursor_map = {}
        self._sheet_name_data_id_list_map = {}  # 记录每个sheet中每个DataID的所有行号
        self._sheet_name_path_str_map_list = []  # 记录sheet名称和字段的对应关系（创建目录使用）
        self._path_str_t_map = {}
        self._wb = None
        if self._mode == DataMode.WRITE or self._mode == DataMode.TEMPLATE:
            if template is None:
                raise ValueError(f'A template must be provided in {self._mode.description} mode.')
            self._load_template(template, exclude=exclude)

    def _load_template(self, template: Template, exclude=None):
        self._template = template
        self._wb = Workbook()  # 新建一个工作簿（即使是read模式，也要先建立一个以记录字段的位置）
        self._create_meta_sheet(is_template_mode=self._mode == DataMode.TEMPLATE)
        self._wb.remove(self._wb.active)  # 删除自带的第一个sheet

        if self._mode == DataMode.WRITE and exclude:  # 导出模式时，先用exclude列表处理template
            exclude_fields(template, exclude)
        ws = self._create_data_sheet(self._root_data_name)
        _ord = template.content.get('_ord', filter(lambda x: not x.startswith('_'), template.content.keys()))
        start = _ord[0]
        end = _ord[-1]
        for field_name in _ord:
            self._create_headers_from_field(ws, [field_name], t=template.content[field_name]['t'],
                                            misc=template.content[field_name].get('misc', {}),
                                            at_start=field_name == start, at_end=field_name == end)
        [x.move_to_new_line(row=2, col_start_at=2) for x in self._sheet_name_cursor_map.values()]

    def _adjust_format(self):
        """
        调整列宽、居中的样式
        :return:
        """
        alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        for sheet_name in self._wb.sheetnames:
            sheet = self._wb[sheet_name]
            if sheet_name == self._meta_sheet_name:
                for index, col in enumerate(sheet.columns):
                    if self._mode == DataMode.TEMPLATE and index == len(DataMetaFieldEnum.reader_meta_fields()) + 1:
                        continue
                    column_name = col[0].column  # 获取列号
                    max_width = 0
                    for cell in col:
                        try:  # 防止单元格没有内容
                            cell.alignment = alignment
                            cell_width = len(cell.value.encode('utf-8'))
                            if cell_width > max_width:
                                max_width = cell_width
                        except:
                            pass
                    adjusted_width = max_width
                    try:
                        sheet.column_dimensions[column_name].width = adjusted_width * 1.15
                    except:
                        pass
            else:
                for col in sheet.columns:
                    column_name = col[0].column  # 获取列号
                    max_width = 0
                    for cell in col:
                        try:  # 防止单元格没有内容
                            cell.alignment = alignment
                            links = cell.value.split('\n')
                            if len(links) > 0:
                                cell_width = len(links[0].encode('utf-8'))
                            else:
                                cell_width = len(cell.value.encode('utf-8'))
                            if cell_width > max_width:
                                max_width = cell_width
                        except:
                            pass
                    adjusted_width = max_width
                    try:
                        sheet.column_dimensions[column_name].width = adjusted_width * 1.15
                    except:
                        pass

    def save(self, path):
        """
        保存excel
        :param path: 路径
        :return:
        """
        self._create_map_sheet()
        self._adjust_format()
        self._wb.save(path)

    """
    导出专用函数
    """

    def write_data(self, data_meta: DataMeta, short_data_id=None):
        """
        添加一个数据
        :param data_meta: 数据的元数据
        :param short_data_id: 导出的时候用来代替过长的DataID
        :return: None
        """
        if self._mode != DataMode.WRITE:
            raise ValueError(f"Cannot write excel in {self._mode.description} mode.")
        if not isinstance(data_meta, DataMeta):
            raise ValueError("data_meta should be an instance of DataMeta")
        if data_meta.tid != self._template.id:
            raise ValueError(f'Data\'s tid should be "{self._template.id}", got "{data_meta.tid}" instead')

        data_id = str(data_meta.id)
        short_data_id = data_id if short_data_id is None else short_data_id

        ws = self._wb[self._meta_sheet_name]
        ws.cell(**self._meta_cursor).value = short_data_id  # 元数据sheet第一列是Data ID
        self._meta_cursor.move_right()
        for index, field in enumerate(DataMetaFieldEnum.writer_iterator()):
            # 填写其他元数据
            ws.cell(**self._meta_cursor).value = field.get_value(data_meta)
            self._meta_cursor.move_right()
        self._meta_cursor.move_to_new_line(col_start_at=1)

        def _to_excel_value(value, t, field_misc=None):
            """
            将数据转化为合适的输出格式，返回相应的 string
            :param value: 实际存储的值
            :param t: 字段类型
            :param field_misc: 字段其它属性信息
            :return: 返回相应的 string
            """
            if value is None:
                return None
            if field_misc is None:
                field_misc = {}
            p_str = smart_text(value)

            if not isinstance(field_misc, dict):
                raise ValueError('Field misc must be a `dict`.')

            if t == TemplateFieldEnum.RANGE:
                r_type = field_misc.get('type')
                if r_type not in (0, 1):
                    raise ValueError('Range type must be 0 or 1')
                if field_misc.get('type'):
                    val = value.get('val')
                    err = value.get('err')
                    if val is None and err is None:
                        raise ValueError('`value` and `error` should be set')
                    p_str = '%s ± %s' % (smart_text(val), smart_text(err))
                else:
                    lb = value.get('lb')
                    ub = value.get('ub')
                    if lb is None and ub is None:
                        raise ValueError('`value` and `error` should be set')
                    p_str = '(%s, %s)' % (smart_text(lb), smart_text(ub))

            elif t == TemplateFieldEnum.IMAGE or t == TemplateFieldEnum.FILE:
                if not isinstance(value, (list, tuple)):
                    raise ValueError('Value should be list/tuple')
                files = []
                for path in value:
                    url = settings.SITE_ADDR + path
                    files.append(url)
                p_str = '\n'.join(files)
            else:
                pass

            return p_str

        def _add_field(field_path_list, field_data, fillings_at_start=(short_data_id,),
                       force_new_line_when_done=False) -> str:
            """
            填写一个字段的数据
            :param field_path_list:  字段的path（列表形式）
            :param field_data: 字段的原始数据
            :param fillings_at_start: 开始若干列手动填充的数据（例如Data ID，Table ID）
            :param force_new_line_when_done: 填写完该字段后是否强制换行
            :return: 链接到cell所在行首个cell的超链接字符串
            """
            path_str = '.'.join(field_path_list)
            template_field = self._path_str_field_meta_map[path_str]
            t = TemplateFieldEnum(template_field['t'])
            value = None  # 对应cell填写的内容，初始为空，待解析
            meta = self._path_str_field_meta_map['.'.join(field_path_list)]
            sheet = self._get_sheet_by_field_path(field_path_list)  # 找到该字段对应的sheet
            coordinate = self._get_field_coordinate_by_field_path(field_path_list)  # 找到该字段表头的坐标
            cursor = self._get_cursor_by_field_path(field_path_list)  # 找到sheet的cursor
            cell = sheet.cell(row=cursor.row, column=coordinate.column)  # 需要填写的cell

            if field_data is not None:
                if t.is_table:
                    if isinstance(field_data, dict):
                        table_id = field_data['val']
                    else:
                        table_id = field_data
                    rows = DataFieldTableRow.objects(_owner_id=table_id)
                    headers = meta.get('misc', {})['_head']
                    hyperlinks = []
                    for row in rows:
                        for head in headers:
                            # 表格元素除了填充Data ID，还需要填充Table ID（如果在数组中）
                            if coordinate.in_array:
                                fillings = fillings_at_start + (str(table_id),)
                            else:
                                fillings = fillings_at_start
                            hyperlink = _add_field(field_path_list + [head], getattr(row, head, None),
                                                   fillings_at_start=fillings)
                            hyperlinks.append(hyperlink)
                    if len(hyperlinks):
                        # 只需要第一个元素超链接
                        value = '=HYPERLINK("{}", "{}")'.format(hyperlinks[0], str(table_id))
                        cell.style = 'Hyperlink'
                elif t.is_container:
                    if isinstance(field_data, dict):
                        container_id = field_data['val']
                    else:
                        container_id = field_data
                    container_content = DataFieldContainer.objects.get(id=container_id)
                    hyperlinks = []
                    for field in template_field['misc']['_ord']:
                        hyperlinks.append(_add_field(field_path_list + [field], getattr(container_content, field, None)))
                    if len(hyperlinks):
                        value = '=HYPERLINK("{}", "{}")'.format(hyperlinks[0], str(container_id))
                        cell.style = 'Hyperlink'
                elif t.is_generator:
                    hyperlink = _add_field(field_path_list + [field_data['sel']], field_data=field_data['val'])
                    value = '=HYPERLINK("{}", "{}")'.format(hyperlink, t.description)
                    cell.style = 'Hyperlink'
                elif t.is_array:
                    hyperlinks = []
                    for index, element in enumerate(field_data):
                        # 数组元素除了填充Data ID还需要填充Item Index
                        hyperlinks.append(
                            _add_field(
                                field_path_list + [ugettext(TemplateFieldEnum(meta.get('misc', {})['t']).description)],
                                field_data=element, fillings_at_start=fillings_at_start + (str(index + 1),)))
                    if len(hyperlinks):
                        value = '=HYPERLINK("{}", "{}")'.format(hyperlinks[0], t.description)
                        cell.style = 'Hyperlink'
                else:  # 基本类型
                    value = _to_excel_value(field_data, meta['t'], meta.get('misc', {}))
                if value is not None:
                    cell.value = str(value)
            # 如果是第一个数据字段，同时填充其左侧的Data ID及其他字段
            if coordinate.at_start:
                t_cursor = cursor.get_line_head()
                for filling in fillings_at_start:
                    sheet.cell(**t_cursor).value = filling
                    t_cursor.move_right()
            line_head = cursor.get_line_head()
            # 如果是最后一个字段，或者强制换行（生成器的字段）时，换行
            if coordinate.at_end or force_new_line_when_done:
                cursor.move_to_new_line()
            # 返回超链接，链接到本行第一个cell
            hyperlink = '#{}!{}'.format(sheet.title, line_head.to_coordinate())
            return hyperlink

        try:
            content = DataContent.objects.get(pk=data_meta.dc_id)
        except DoesNotExist:
            content = {}  # TODO 找不到

        for field in self._template.content['_ord']:
            _add_field([field], getattr(content, field, None))

    """
    导入专用函数
    """

    def _get_field_row_list(self, path_str_or_list, data_id):
        return self._sheet_name_data_id_list_map[self._get_sheet_name_by_field_path(path_str_or_list)].get(
            data_id)

    def read_data(self, excel_path_or_fp, author, file_dir=None):
        """
        用excel中读取数据
        :param excel_path_or_fp: excel文件路径或者fp
        :param author: 上传的用户名
        :param file_dir: 数据中图片和文件所在的文件夹
        :return: DataMeta列表
        """
        if self._mode != DataMode.READ:
            raise ValueError("Cannot read excel in %s mode." % (self._mode.description,))

        data_id = 0  # 当前正在解析的数据ID
        current_sheet_name = ""  # 当前正在解析的sheet名称
        current_row_num = 0  # 当前正在解析的行号
        current_column_num = 0  # 当前正在解析的列号
        current_path_str = ""  # 当前正在解析的字段path
        parser = Parser(file_dir)

        def _raise_parsing_exception(error, o1=None, o2=None, o3=None):
            """
            报错
            :param error: ParsingErrorEnum枚举
            :param o1: 参数1
            :param o2: 参数2
            :param o3: 参数3
            :return:
            """
            kwargs = {}
            if o1:
                kwargs['o1'] = o1
            if o2:
                kwargs['o1'] = o1
            if o3:
                kwargs['o3'] = o3

            if current_sheet_name and current_row_num and current_column_num:
                suffix = ugettext(' (Cell %(column_letter)s%(row)s in sheet "%(sheet_name)s")') % {
                    'sheet_name': current_sheet_name,
                    'column_letter': get_column_letter(
                        current_column_num), 'row': current_row_num}
            else:
                suffix = ""
            if isinstance(error, ParsingErrorEnum):
                e = ParsingError(error, **kwargs)
            else:
                e = error
            e.message += suffix
            raise e

        def _construct_field(field_meta, value):
            """
            根据值构造json中的字段
            :param field_meta: 字段元信息
            :param value: 字段的值
            :return: json中的字段
            """
            t = TemplateFieldEnum(field_meta['t'])
            v = {}
            if t.is_table:
                v = value
            elif t.is_array:
                v = value
            elif t.is_generator:
                v['sel'] = list(value.keys())[0]
                v['val'] = list(value.values())[0]
            elif t.is_container:
                v = value
            else:
                try:
                    v = parser.parse(current_path_str, field_meta, value)
                except ParsingError as e:
                    _raise_parsing_exception(e)
            return v

        def _read_field(field_path_list, at_row, array_index=0, check_value_only=False):
            """
            读取某个字段的值
            :param field_path_list: 字段path列表
            :param at_row: 要读取的值所在的行号
            :param array_index: 读取的值在数组中的序号（如果是数组的元素，或者是内嵌在数组元素中），下标从1开始
            :param check_value_only: 如果为True，不递归读取，仅读取单元格的值（用于生成器中判断用户选择了那个元素）
            :return: 字段的json值
            """
            path_str = '.'.join(field_path_list)
            field_meta = self._path_str_field_meta_map[path_str]
            field_coordinate = self._path_str_coordinate_map[path_str]
            current_sheet = self._get_sheet_by_field_path(field_path_list)
            nonlocal current_sheet_name, current_column_num, current_path_str
            current_column_num = field_coordinate.column
            current_sheet_name = current_sheet.title
            current_path_str = path_str
            t = TemplateFieldEnum(field_meta['t'])
            required = field_meta['misc'].get('r')
            raw_data = None
            if check_value_only:  # TODO 是否可以删除？
                return _force_text(current_sheet.cell(row=at_row, column=field_coordinate.column).value)

            if t.is_container:
                _sub_ord = field_meta['misc'].get('_ord',
                                                  list(filter(lambda x: not x.startswith('_'),
                                                              field_meta['misc'].keys())))

                raw_data = {}
                # 取出容器子字段的row_list(所有子字段都在同一个sheet所以可以用第一个字段_sub_ord[0]取）
                row_list = self._get_field_row_list(field_path_list + [_sub_ord[0]],
                                                    data_id)
                if row_list is None:
                    if required:
                        _raise_parsing_exception(ParsingErrorEnum.VALUE_MISSING, o1=path_str)
                    return None
                for sub_field_name in _sub_ord:
                    if array_index:  # 数组中的第array_index个元素
                        read_row_at = row_list[array_index - 1]
                    else:
                        read_row_at = row_list[0]
                    raw_data[sub_field_name] = _read_field(field_path_list + [sub_field_name],
                                                           at_row=read_row_at,
                                                           array_index=array_index)

            elif t.is_generator:
                raw_data = {}
                _sub_ord = field_meta['misc'].get('_ord', list(
                    filter(lambda x: not x.startswith('_'), field_meta['misc'].keys())))
                row_list = self._get_field_row_list(field_path_list + [_sub_ord[0]], data_id)
                if row_list is None:
                    if required:
                        _raise_parsing_exception(ParsingErrorEnum.VALUE_MISSING, o1=path_str)
                    return None
                for sub_field_name in _sub_ord:
                    if array_index:
                        read_row_at = row_list[array_index - 1]
                    else:
                        read_row_at = row_list[0]
                    d = _read_field(field_path_list + [sub_field_name], at_row=read_row_at,
                                    check_value_only=True, array_index=array_index)
                    if d is not None:
                        raw_data[sub_field_name] = _read_field(field_path_list + [sub_field_name], at_row=read_row_at,
                                                               array_index=array_index)
                        break
                else:
                    return None

            elif t.is_table:
                headers = field_meta['misc']['_head']
                sub_sheet = self._get_sheet_by_field_path(field_path_list + [headers[0]])
                table_cursor: TableRowCursor = self._path_str_table_cursor_map[path_str]
                raw_data = []
                while table_cursor.has_next_row(sub_sheet, data_id, ignore_table_id=not field_coordinate.in_array):
                    raw_data.append({})
                    for header in headers:
                        raw_data[-1][header] = _read_field(field_path_list + [header], table_cursor.row)
                if len(raw_data) == 0:
                    return None
            elif t.is_array:
                element_path_list = field_path_list + [ugettext(TemplateFieldEnum(field_meta['misc']['t']).description)]
                row_list = self._get_field_row_list(element_path_list, data_id)
                if row_list is None:
                    if required:
                        _raise_parsing_exception(ParsingErrorEnum.VALUE_MISSING, o1=path_str)
                raw_data = []
                for index, row_number in enumerate(row_list):
                    element = _read_field(element_path_list, row_number, array_index=index + 1)
                    if element is not None:
                        raw_data.append(element)
                if len(raw_data) == 0:
                    if required:
                        _raise_parsing_exception(ParsingErrorEnum.NO_ELEMENTS_IN_ARRAY, o1=path_str)
                    else:
                        return None
            else:
                raw_data = _force_text(current_sheet.cell(row=at_row, column=field_coordinate.column).value)
            value = _construct_field(field_meta, raw_data)
            return value

        def _read_meta():
            ws = self._wb[self._meta_sheet_name]
            md = {}
            for meta_row in ws.iter_rows(min_row=2):
                temp_meta = {'tid': template_id}
                for index, field in enumerate(DataMetaFieldEnum.reader_iterator()):
                    value = _force_text(meta_row[index + 1].value)
                    if value is not None:
                        temp_meta[field.field_raw_name] = value

                source = dict(source=temp_meta.get('source'))
                temp_meta['source'] = {}
                source['project'] = temp_meta.get('project')
                source['others'] = temp_meta.get('others')
                source['reference'] = temp_meta.get('reference')
                for k, v in source.items():
                    if v is not None:
                        temp_meta['source'][k] = v
                temp_meta.pop('project', None)
                temp_meta.pop('reference', None)
                temp_meta.pop('others', None)
                temp_meta.pop(None, None)
                md[_force_text(meta_row[0].value)] = temp_meta
            md.pop(None, None)
            return md

        def _read_data():
            dd = {}
            for row in self._wb[self._root_data_name].iter_rows(min_row=2):
                nonlocal current_row_num, data_id
                current_row_num = row[0].row
                data_id = _force_text(row[0].value)
                if data_id is None:
                    continue
                data_id = str(data_id)
                _ord = self._template.content.get('_ord',
                                                  filter(lambda x: not x.startswith('_'),
                                                         self._template.content.keys()))
                data = {}
                for field_name in _ord:
                    data[field_name] = _read_field([field_name], at_row=row[0].row)
                    dd[data_id] = data
                parser.next_data()
            return dd

        wb = load_workbook(excel_path_or_fp)
        meta_sheet = wb[self._meta_sheet_name]
        template_id = _force_text(meta_sheet.cell(row=1, column=len(DataMetaFieldEnum.reader_meta_fields()) + 2).value)
        try:
            r = re.search('^[1-9]\d*', template_id)
            template_id = r.group()
            if template_id == '':
                raise TypeError()
            template = Template.objects.get(id=template_id)
            self._load_template(template)
        except (TypeError, AttributeError):
            _raise_parsing_exception(ParsingErrorEnum.BAD_TEMPLATE)
        except Template.DoesNotExist:
            _raise_parsing_exception(ParsingErrorEnum.TEMPLATE_NOT_FOUND, o1=template_id)

        self._wb = wb
        # 记录每个sheet每个数据ID所在的行
        for sheet_name in self._wb.sheetnames:
            sheet = self._wb[sheet_name]
            self._sheet_name_data_id_list_map[sheet_name] = {}
            for row in sheet.iter_rows(min_col=1, max_col=1, min_row=2):
                data_id = _force_text(row[0].value)
                data_row_number_list = self._sheet_name_data_id_list_map[sheet_name].get(data_id)
                if data_row_number_list is None:
                    data_row_number_list = []
                    self._sheet_name_data_id_list_map[sheet_name][data_id] = data_row_number_list
                data_row_number_list.append(row[0].row)

        meta_dict = _read_meta()  # 元数据，key为数据id
        data_dict = _read_data()  # 数据，key为数据id
        data_len = len(data_dict.keys())
        meta_len = len(meta_dict.keys())
        if data_len != meta_len:
            data_set = set(data_dict.keys())
            meta_set = set(meta_dict.keys())
            if data_len > meta_len:
                _raise_parsing_exception(ParsingErrorEnum.NO_META, o1=', '.join(data_set - meta_set))
            else:
                _raise_parsing_exception(ParsingErrorEnum.NO_DATA, o1=', '.join(meta_set - data_set))

        dm_list = []
        template = Template.objects.get(pk=template_id)
        js = JSONSerializer(template)
        for data_id in data_dict:
            meta = meta_dict[data_id]
            content = data_dict[data_id]
            dm = js.get_deserialized_obj(dict(meta=meta, content=content), wd=file_dir)
            dm_list.append(dm)
        return dm_list

    """
    生成模板专用函数
    """

    def generate_template(self, number_of_data=3, elements_per_array=1, rows_per_table=1):
        if self._mode != DataMode.TEMPLATE:
            raise ValueError(f'Cannot generate template in {self._mode.description} mode')

        for data_count in range(1, number_of_data + 1):
            data_id = ugettext('Data ID of data NO.%(number)s') % {'number': data_count}
            ws = self._wb[self._meta_sheet_name]
            ws.cell(**self._meta_cursor).value = data_id  # 第一列是Data ID
            self._meta_cursor.move_right()

            for index, field in enumerate(DataMetaFieldEnum.reader_iterator()):
                # 填写其他元数据
                ws.cell(**self._meta_cursor).value = ugettext('Enter %(field_name)s here') % {
                    'field_name': field.description}
                self._meta_cursor.move_right()

            self._meta_cursor.move_to_new_line(col_start_at=1)

            def _add_field(field_path_list, fillings_at_start=(data_id,), force_filling=False):

                path_str = '.'.join(field_path_list)
                template_field = self._path_str_field_meta_map[path_str]
                t = TemplateFieldEnum(template_field['t'])
                meta = self._path_str_field_meta_map['.'.join(field_path_list)]
                sheet = self._get_sheet_by_field_path(field_path_list)  # 找到该字段对应的sheet
                coordinate = self._get_field_coordinate_by_field_path(field_path_list)  # 找到该字段表头的坐标
                cursor = self._get_cursor_by_field_path(field_path_list)  # 找到sheet的cursor
                cell = sheet.cell(row=cursor.row, column=coordinate.column)  # 需要填写的cell
                value = ''
                if t.is_table:
                    headers = meta['misc']['_head']
                    for i in range(1, rows_per_table + 1):
                        for head in headers:
                            fillings = fillings_at_start + (
                                ugettext('Enter table id here'),) if coordinate.in_array else fillings_at_start
                            _add_field(field_path_list + [head], fillings_at_start=fillings)
                elif t.is_container:
                    for field in meta['misc']['_ord']:
                        _add_field(field_path_list + [field])
                elif t.is_generator:
                    for field_name in meta['misc']['_opt']:
                        _add_field(field_path_list + [field_name], force_filling=True)
                elif t.is_array:
                    for i in range(1, elements_per_array + 1):
                        _add_field(
                            field_path_list + [ugettext(TemplateFieldEnum(meta.get('misc', {})['t']).description)],
                            fillings_at_start=fillings_at_start + ('',))
                elif t.is_choice:
                    value = ugettext('Select %(field_name)s from the drop-down list.') % {
                        'field_name': field_path_list[-1],
                        'field_type': t.description
                    }
                else:  # 基本类型
                    example = ''
                    if t.is_range:
                        if meta.get('type'):
                            example = ugettext('(Eg. a ± b)')
                        else:
                            example = ugettext('(Eg. (a, b) Parentheses are required.)')

                    value = ugettext("Enter %(field_name)s(%(field_type)s) here. ") % {
                        'field_name': field_path_list[-1],
                        'field_type': t.description
                    }
                    value += example

                if force_filling:
                    value = ugettext("Enter %(field_name)s(%(field_type)s) here. ") % {
                        'field_name': field_path_list[-1],
                        'field_type': t.description
                    }
                cell.value = value
                # 如果是第一个数据字段，同时填充其左侧的Data ID及其他字段
                if coordinate.at_start:
                    t_cursor = cursor.get_line_head()
                    for filling in fillings_at_start:
                        sheet.cell(**t_cursor).value = filling
                        t_cursor.move_right()
                if coordinate.at_end:
                    cursor.move_to_new_line()

            content = self._template.content
            for field in content['_ord']:
                _add_field([field])


'''
lyp为了文件上传做的添加
'''


class ExcelFieldSerializer(FieldSerializer):

    def serialize_field(self, mongo_value, field_meta):
        if mongo_value is None:
            return None
        else:
            return super().serialize_field(mongo_value, field_meta)

    def serialize_number(self, mongo_value, field_meta):
        return mongo_value

    def serialize_range(self, mongo_value, field_meta):
        field_type = field_meta.field_type
        if field_type.is_range_interval(field_meta.range_type):
            lb = mongo_value.get('lb', '-∞')
            ub = mongo_value.get('ub', '+∞')
            return f'({lb}, {ub})'
        else:
            err = mongo_value.get('err', '?')
            val = mongo_value.get('val', '?')
            return f'{val}±{err}'

    def serialize_image(self, mongo_value, field_meta):
        return '\n'.join([settings.SITE_ADDR +
                          (settings.MEDIA_URL if not x.startswith('/api/') else '') + x for x in mongo_value])

    def serialize_file(self, mongo_value, field_meta):
        return '\n'.join([settings.SITE_ADDR +
                          (settings.MEDIA_URL if not x.startswith('/api/') else '') + x for x in mongo_value])

    def serialize_choice(self, mongo_value, field_meta):
        return str(mongo_value)
